﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Caching Result Set Data Sample</title>
    
    <link rel="stylesheet" type="text/css" href="../local/Classic.css">
      
    </link>
    
    <script src="../local/script.js">
      
    </script><script src="../local/script_main.js">&amp;nbsp;</script>
  </head>
  <body>
    <!--Topic built:09/24/2007 09:01:38-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="headerBold">Caching Result Set Data Sample</span>
          </td>
          <td align="right">
            
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      
        
        
		<font color="DarkGray"><!-- [This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]<br/>[Documentation built on $$TIMESTAMP$$]  --> </font> <p /> <p />
	
        <div id="introductionSection" class="section">
    <p>This Microsoft SQL Server 2005 JDBC Driver sample application demonstrates how to retrieve a large set of data from a database, and then control the number of rows of data that are cached on the client by using the <a href="233bf4f8-4758-42d0-a80b-33e34fa78027.htm">setFetchSize</a> method of the <a href="eaffcff1-286c-459f-83da-3150778480c9.htm">SQLServerResultSet</a> object. </p>
    <div style="margin: .5em 1.5em .5em 1.5em"><b>Note: </b>
      Limiting the number of rows cached on the client is different from limiting the total number of rows that a result set can contain. To control the total number of rows that are contained in a result set, use the <a href="cccc0667-589b-4655-8ea8-14ae8b2eb9dc.htm">setMaxRows</a> method of the <a href="ec24963c-8b51-4838-91e9-1fbfa2347451.htm">SQLServerStatement</a> object, which is inherited by both the <a href="a8481c06-fbba-432b-8c69-4f4619c20ad4.htm">SQLServerPreparedStatement</a> and <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a> objects.<p />
    </div>
    <p>To set a limit on the number of rows cached on the client, you must first use a server-side cursor when you create one of the <b>Statement</b> objects by specifically stating the cursor type to use when creating the <b>Statement</b> object. For example, the JDBC driver provides the TYPE_SS_SERVER_CURSOR_FORWARD_ONLY cursor type, which is a fast forward-only, read-only server-side cursor for use with SQL Server databases.</p>
    <div style="margin: .5em 1.5em .5em 1.5em"><b>Note: </b>
      An alternative to using the SQL Server specific cursor type is to use the selectMethod connection string property, setting its value to "cursor". For more information about the cursor types supported by the JDBC driver, see <a href="4f4d3db7-4f76-450d-ab63-141237a4f034.htm">Understanding Cursor Types</a>.<p />
    </div>
    <p>After you have run the query contained in the <b>Statement</b> object and the data is returned to the client as a result set, you can call the <b>setFetchSize</b> method to control how much data is retrieved from the database at one time. For example, if you have a table that contains 100 rows of data, and you set the fetch size to 10, only 10 rows of data will be cached on the client at any point in time. Although this will slow down the speed at which the data is processed, it has the advantage of using less memory on the client, which can be especially useful when you need to process large amounts of data.</p>
    <p>The code file for this sample is named cacheRS.java, and it can be found in the following location:</p>
    <p>&lt;<i>installation directory</i>&gt;\sqljdbc_&lt;<i>version</i>&gt;\&lt;<i>language</i>&gt;\help\samples\resultsets</p>
  </div><h1 class="heading">Requirements</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">To run this sample application, you must set the classpath to include the sqljdbc.jar file. If the classpath is missing an entry for sqljdbc.jar, the sample application will throw the common "Class not found" exception. You will also need access to the SQL Server 2005 AdventureWorks sample database.</p>
      <p xmlns="">For more information about how to set the classpath, see <a href="6faaf05b-8b70-4ed2-9b44-eee5897f1cd0.htm">Using the JDBC Driver</a>.</p>
    </content>
  </div><h1 class="heading">Example</h1><div id="codeExampleSection" class="section">
    <description xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <content>
        <p xmlns="">In the following example, the sample code makes a connection to the AdventureWorks sample database. Then it uses an SQL statement with the <a href="ec24963c-8b51-4838-91e9-1fbfa2347451.htm">SQLServerStatement</a> object, specifies the server-side cursor type, and then runs the SQL statement and places the data that it returns into a <b>SQLServerResultSet</b> object.</p>
        <p xmlns="">Next, the sample code calls the custom <b>timerTest</b> method, passing as arguments the fetch size to use and the result set. The <b>timerTest</b> method then sets the fetch size of the result set by using the <b>setFetchSize</b> method, sets the start time of the test, and then iterates through the result set with a<code> While </code>loop. As soon as the<code> While </code>loop is exited, the code sets the stop time of the test, and then displays the result of the test including the fetch size, the number of rows processed, and the time it took to execute the test.</p>
      </content>
    </description>
    <div class="sampleCode"><span codeLanguage="other"><pre>import java.sql.*;
import com.microsoft.sqlserver.jdbc.SQLServerResultSet;

public class cacheRS {

   public static void main(String[] args) {

      // Create a variable for the connection string.
      String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
            "databaseName=AdventureWorks;integratedSecurity=true;";

      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {

         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         con = DriverManager.getConnection(connectionUrl);

         // Create and execute an SQL statement that returns a large
         // set of data and then display it.
         String SQL = "SELECT * FROM Sales.SalesOrderDetail;";
         stmt = con.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, +
               SQLServerResultSet.CONCUR_READ_ONLY);

         // Perform a fetch for every row in the result set.
         rs = stmt.executeQuery(SQL);
         timerTest(1, rs);
         rs.close();

         // Perform a fetch for every tenth row in the result set.
         rs = stmt.executeQuery(SQL);
         timerTest(10, rs);
         rs.close();

         // Perform a fetch for every 100th row in the result set.
         rs = stmt.executeQuery(SQL);
         timerTest(100, rs);
         rs.close();

         // Perform a fetch for every 1000th row in the result set.
         rs = stmt.executeQuery(SQL);
         timerTest(1000, rs);
         rs.close();

         // Perform a fetch for every 128th row (the default) in the result set.
         rs = stmt.executeQuery(SQL);
         timerTest(0, rs);
         rs.close();
      }

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }

      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }

   public static void timerTest(int fetchSize, ResultSet rs) {
      try {

         // Declare the variables for tracking the row count and elapsed time.
         int rowCount = 0;
         long startTime = 0;
         long stopTime = 0;
         long runTime = 0;

         // Set the fetch size then iterate through the result set to
         // cache the data locally.
         rs.setFetchSize(fetchSize);
         startTime = System.currentTimeMillis();
         while (rs.next()) {
            rowCount++;
         }
         stopTime = System.currentTimeMillis();
         runTime = stopTime - startTime;

         // Display the results of the timer test.
         System.out.println("FETCH SIZE: " + rs.getFetchSize());
         System.out.println("ROWS PROCESSED: " + rowCount);
         System.out.println("TIME TO EXECUTE: " + runTime);
         System.out.println();

      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}</pre></span></div>
  </div><span id="seeAlsoSpan"><h1 class="heading">See Also</h1></span><div id="seeAlsoSection" class="section" name="collapseableSection"><a href="4fc4b1c6-3075-4ad7-9244-865d9ede7ae6.htm">Working with Result Sets</a><br /><br /></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]-->
      <div id="footer" class="section">
        
		<hr />
		
		<span id="fb" class="feedbackcss">
			
			
		</span>
		
		<a href="9bad553b-9e70-4696-8499-2e35f772a1e0.htm">
			
			© 2007 Microsoft Corporation. All rights reserved.
		</a>
 	
	
      </div>
    </div>
  </body>
</html>